#!/usr/bin/perl -w
use strict;

my $MERGED_SORTED_FILE='merged_sorted.tsv';
my $MCC_COUNTRY_OUT_FILE='mcc2country_new.tsv';
my $MAP_OUT_FILE='MccMncOperatorCountry_new.tsv';
my $ORACLE_SQL_OUT_FILE='MccMncOperatorCountry_update.sql';

my $CURRENT_MAP_MCC_MNC_OPERATOR_COUNTRY_FILE = 'MccMncOperatorCountry.tsv';
my $CURRENT_ORACLE_MCC_MNC_OPERATOR_COUNTRY_FILE = 'oracle_original_operator_dim.txt';

open(MSFD, $MERGED_SORTED_FILE) or die;
open(MCC_COUNTRY_OUT, '>', $MCC_COUNTRY_OUT_FILE) or die;
open(MAP_OUT, '>', $MAP_OUT_FILE) or die;
open(ORACLE_SQL_OUT, '>', $ORACLE_SQL_OUT_FILE) or die;

my $ACCEPTABLE_NUMBER_UNKNOWN_COUNTRIES = 27;

my %mcc_country_db;

## Generate mcc2country.tsv
my $er2 = 0;
while (my $data1 = <MSFD>) {
    chomp $data1;

    ## QA ##
    # check format of new data.
    unless ($data1 =~ /(1|^\d{3})\t\d{1,3}\t.*\t\w{2}$/) {
	print "Error: [$data1] does not look right\n";
	$er2++;
    }
    if ($er2 > 0) {
	print "$er2 Errors occured with propoosed new table:$MERGED_SORTED_FILE\n";
	exit 1;
    }

    my @a1 = split(/\t/, $data1);
    my $mcc = $a1[0];
    my $mnc = $a1[1];
    my $operator = $a1[2];
    my $country = $a1[3];

    if (defined $mcc_country_db{$mcc}) {
	# there's an entry, but how does it compare to mcc. Has to be same.
	if ($mcc_country_db{$mcc} ne $country) {
	    print "Error: new $mcc/$mnc $operator is supposed to be country $mcc_country_db{$mcc} but is showing up as county $country. Please check this out in $MERGED_SORTED_FILE.\n";
	    exit 1;
	}

    } else {
	$mcc_country_db{$mcc} = $country;
    }
}
my @mcc_country = sort {$a cmp $b} keys %mcc_country_db;

my $mcc_country_key_index = 0;
foreach my $mcc_key (@mcc_country) {
    $mcc_country_key_index++;
    print MCC_COUNTRY_OUT "$mcc_country_key_index\t$mcc_key\t$mcc_country_db{$mcc_key}\n";
}

close MCC_COUNTRY_OUT;
close MSFD;    


## Read MccMncOperatorCountry.tsv
open(CURRENT_MAP_MCC_MNC_OPERATOR_COUNTRY, $CURRENT_MAP_MCC_MNC_OPERATOR_COUNTRY_FILE) or die;

# need to generate this oracle file fresh. See admin/bhok/oracle for details of spooling.
open(CURRENT_ORACLE_MCC_MNC_OPERATOR_COUNTRY, $CURRENT_ORACLE_MCC_MNC_OPERATOR_COUNTRY_FILE);

# generated with multiple scripts. Do not modify oracle_country_dim.txt unless you discover
# that a country name is wrong. Check for ZZ (unknown country) in any output.
open(MSFD, $MERGED_SORTED_FILE) or die;

##############################################################################
# Read tables into memory
##############################################################################

# Store current operator map table
my %current_map_mcc_mnc_operator_db;
my $dup_delta = 0;
while (my $current_map_mcc_mnc_operator_line = <CURRENT_MAP_MCC_MNC_OPERATOR_COUNTRY>) {
    chomp $current_map_mcc_mnc_operator_line;
    my @a1 = split(/\t/, $current_map_mcc_mnc_operator_line);
    my $operator_id = $a1[0];
    my $mcc = $a1[1];
    my $mnc = $a1[2];
    my $operator = $a1[3];
    my $country = $a1[4];
    
    if (defined $current_map_mcc_mnc_operator_db{"$mcc\t$mnc"}) {
	print "$mcc\t$mnc already defined in original map file\n";
	$dup_delta++;
    }

    $current_map_mcc_mnc_operator_db{"$mcc\t$mnc"} = { OPERATOR_ID => $operator_id,
						      MCC => $mcc,
						      MNC => $mnc,
						      OPERATOR => $operator,
						      COUNTRY => $country };

}

# Store what's current in oracle DIM_POSITION_OPERATOR table.Need to extract from Oracle.
my %current_oracle_mcc_mnc_operator_db;
while (my $oracle_mcc_mnc_operator_line = <CURRENT_ORACLE_MCC_MNC_OPERATOR_COUNTRY>) {
    chomp $oracle_mcc_mnc_operator_line;
    next if ($oracle_mcc_mnc_operator_line =~ /^\s*$/);

    my @a1 = split(/\|/, $oracle_mcc_mnc_operator_line);
    $a1[4] =~ s/\s+$//;
    my $operator_id = $a1[0];
    my $mcc = $a1[1];
    my $mnc = $a1[2];
    my $operator = $a1[3];
    my $country = $a1[4];

    $current_oracle_mcc_mnc_operator_db{"$mcc\t$mnc"} = { OPERATOR_ID => $operator_id,
						     MCC => $mcc,
						     MNC => $mnc,
						     OPERATOR => $operator,
						     COUNTRY => $country };

}

##############################################################################
# QA current Oracle table against current MccMncOperator map
##############################################################################
# check sizes of current map table = size of what's in oracle.
my $size_current_oracle_mcc_mnc_operator_db = keys %current_oracle_mcc_mnc_operator_db;
my $size_current_map_mcc_mnc_operator_db = keys %current_map_mcc_mnc_operator_db;
if ($size_current_oracle_mcc_mnc_operator_db > $size_current_map_mcc_mnc_operator_db) {
    print "oracle dim_position_operator is larger in size than $ORACLE_SQL_OUT_FILE map file\n";
    exit 1;
} elsif ($size_current_oracle_mcc_mnc_operator_db < $size_current_map_mcc_mnc_operator_db) {
    print "oracle dim_position_operator is less than in size than $ORACLE_SQL_OUT_FILE map file\n";
    exit 1;
}
# check that they are the same one-for-one.
my $e1 = 0;
my @keys_current_oracle_mcc_mnc_operator_db = sort { $current_oracle_mcc_mnc_operator_db{$a}->{OPERATOR_ID} <=> $current_oracle_mcc_mnc_operator_db{$b}->{OPERATOR_ID} } keys %current_oracle_mcc_mnc_operator_db;
my @keys_current_map_mcc_mnc_operator_db = sort { $current_map_mcc_mnc_operator_db{$a}->{OPERATOR_ID} <=> $current_map_mcc_mnc_operator_db{$b}->{OPERATOR_ID} } keys %current_map_mcc_mnc_operator_db;
my $size = $size_current_oracle_mcc_mnc_operator_db; # == size_current_map_mcc_mnc_operator_db

for(my $t1 = 0; $t1 < $size; $t1++) {
    if ($current_oracle_mcc_mnc_operator_db{$keys_current_oracle_mcc_mnc_operator_db[$t1]}->{OPERATOR_ID} != $current_map_mcc_mnc_operator_db{$keys_current_map_mcc_mnc_operator_db[$t1]}->{OPERATOR_ID}) {
	print "Warning: position $t1 in either $MAP_OUT_FILE or Oracle DIM_POSITION_OPERATOR " .
	    " do not match operator id\n";
	$e1++;
    }
    if ($current_oracle_mcc_mnc_operator_db{$keys_current_oracle_mcc_mnc_operator_db[$t1]}->{MCC} != $current_map_mcc_mnc_operator_db{$keys_current_map_mcc_mnc_operator_db[$t1]}->{MCC}) {
	print "Warning: position $t1 in either $MAP_OUT_FILE or Oracle DIM_POSITION_OPERATOR " .
	    " do not match MCC\n";
	$e1++
    }
    if ($current_oracle_mcc_mnc_operator_db{$keys_current_oracle_mcc_mnc_operator_db[$t1]}->{MNC} != $current_map_mcc_mnc_operator_db{$keys_current_map_mcc_mnc_operator_db[$t1]}->{MNC}) {
	print "Warning: position $t1 in either $MAP_OUT_FILE or Oracle DIM_POSITION_OPERATOR " .
	    " do not match MNC.\n";
	$e1++;
    }
    if ($current_oracle_mcc_mnc_operator_db{$keys_current_oracle_mcc_mnc_operator_db[$t1]}->{OPERATOR} ne $current_map_mcc_mnc_operator_db{$keys_current_map_mcc_mnc_operator_db[$t1]}->{OPERATOR}) {
	print "Warning: position $t1 in either $MAP_OUT_FILE or Oracle DIM_POSITION_OPERATOR " .
	    " do not match operator name.\n";
	$e1++;
    }
    if ($current_oracle_mcc_mnc_operator_db{$keys_current_oracle_mcc_mnc_operator_db[$t1]}->{COUNTRY} ne $current_oracle_mcc_mnc_operator_db{$keys_current_map_mcc_mnc_operator_db[$t1]}->{COUNTRY}) {
	print "Warning: position $t1 in either $MAP_OUT_FILE or Oracle DIM_POSITION_OPERATOR " .
	    " do not match COUNTRY.\n";
	$e1++;
    }
}
if ($e1 > 0) {
    print "$e1 warnings occured\n";
}
##############################################################################
# got here, current table looks good.
##############################################################################
# New stuff.
my %proposed_mcc_mnc_operator_db;
while (my $data2 = <MSFD>) {
    chomp $data2;
    my @a1 = split(/\t/, $data2);
    my $mcc = $a1[0];
    my $mnc = $a1[1];
    my $operator = $a1[2];
    my $country = $a1[3];

    $proposed_mcc_mnc_operator_db{"$mcc\t$mnc"} = {MCC => $mcc,
						   MNC => $mnc,
						   OPERATOR => $operator,
						   COUNTRY => $country };

}

##############################################################################
# Update tables
##############################################################################
my $operator_cnt = $size + 1 + $dup_delta;

my @pmmo = (sort { $a cmp $b } keys %proposed_mcc_mnc_operator_db);

foreach my $p (@pmmo) {

    unless (defined $current_map_mcc_mnc_operator_db{$p}) {
	# Doesn't exist, add to db.
	$current_map_mcc_mnc_operator_db{$p} = { OPERATOR_ID => $operator_cnt++,
						 MCC => $proposed_mcc_mnc_operator_db{$p}->{MCC},
						 MNC => $proposed_mcc_mnc_operator_db{$p}->{MNC},
						 OPERATOR => $proposed_mcc_mnc_operator_db{$p}->{OPERATOR},
						 COUNTRY => $proposed_mcc_mnc_operator_db{$p}->{COUNTRY},
						 MODE => 'insert'};
    } else {

	# update current db, don't touch operator_id. Just update operator and country if needed.
	if ($current_map_mcc_mnc_operator_db{$p}->{COUNTRY} ne $proposed_mcc_mnc_operator_db{$p}->{COUNTRY}) {
	    print "Will update [$p] country from $current_map_mcc_mnc_operator_db{$p}->{COUNTRY} to $proposed_mcc_mnc_operator_db{$p}->{COUNTRY}\n";
	    $current_map_mcc_mnc_operator_db{$p}->{COUNTRY} = $proposed_mcc_mnc_operator_db{$p}->{COUNTRY};
	    $current_map_mcc_mnc_operator_db{$p}->{MODE} = 'update';
	}
	if ($current_map_mcc_mnc_operator_db{$p}->{OPERATOR} ne $proposed_mcc_mnc_operator_db{$p}->{OPERATOR}) {
	    print "Will update [$p] operator from $current_map_mcc_mnc_operator_db{$p}->{OPERATOR} to $proposed_mcc_mnc_operator_db{$p}->{OPERATOR}\n";
	    $current_map_mcc_mnc_operator_db{$p}->{OPERATOR} = $proposed_mcc_mnc_operator_db{$p}->{OPERATOR};
	    $current_map_mcc_mnc_operator_db{$p}->{MODE} = 'update';
	}
    }
}
##############################################################################
# Produce new tables.
##############################################################################

# Oracle workaround for & (ampersands in data, like AT&T)
print ORACLE_SQL_OUT "SET DEFINE OFF;\n";

my $unknown_country_cnt = 0;
foreach my $final (sort { $current_map_mcc_mnc_operator_db{$a}->{OPERATOR_ID} <=> $current_map_mcc_mnc_operator_db{$b}->{OPERATOR_ID}} keys %current_map_mcc_mnc_operator_db) {
    my $final_operator_id = $current_map_mcc_mnc_operator_db{$final}->{OPERATOR_ID};
    my $final_mcc_mnc = $final; # mcc\tmnc
    my $final_mcc = $current_map_mcc_mnc_operator_db{$final}->{MCC};
    my $final_mnc = $current_map_mcc_mnc_operator_db{$final}->{MNC};
    my $final_operator = $current_map_mcc_mnc_operator_db{$final}->{OPERATOR};

    # fix special Oracle string chars
    my $final_operator_sql = $final_operator;
    # change ' to '' in string.
    $final_operator_sql =~ s/\'/\'\'/g;

    my $final_country = $current_map_mcc_mnc_operator_db{$final}->{COUNTRY};
    my $mode = $current_map_mcc_mnc_operator_db{$final}->{MODE};

    ## QA
    if ($final_country =~ /ZZ/) {
	$unknown_country_cnt++;
    }

    print MAP_OUT "$final_operator_id\t$final_mcc_mnc\t$final_operator\t$final_country\n";

    if (defined $mode) {
	if ($mode eq 'update') {
	    print ORACLE_SQL_OUT "UPDATE DIM_POSITION_OPERATOR SET OPERATOR_NAME = \'$final_operator_sql\', COUNTRY_NAME = \'$final_country\' WHERE MCC = \'$final_mcc\' AND MNC = \'$final_mnc\';\n";
	    
	} elsif ($mode eq 'insert') {
	    
	    print ORACLE_SQL_OUT "INSERT INTO DIM_POSITION_OPERATOR (OPERATOR_ID, MCC, MNC, OPERATOR_NAME, COUNTRY_NAME) VALUES (\'$final_operator_id\', \'$final_mcc\', \'$final_mnc\', \'$final_operator_sql\', \'$final_country\');\n";
	}
    } 
}
print ORACLE_SQL_OUT "commit;\n";

close ORACLE_SQL_OUT;
close MAP_OUT;

print "qty of unknown country names: $unknown_country_cnt\n";

if ($unknown_country_cnt != $ACCEPTABLE_NUMBER_UNKNOWN_COUNTRIES) {
    print "Warning: Number of unknown countries is not the expected number $ACCEPTABLE_NUMBER_UNKNOWN_COUNTRIES.\n";
    print "Look at $MERGED_SORTED_FILE to see if this is correct for mcc/mnc pairs with unknown country - ZZ.\nCheck oracle_country_dim.txt for country name, fix that if necessary by looking at the 2 web pages (mcc_mnc.com and wiki database).\nThen fix global variable ACCEPTABLE_NUMBER_UNKNOWN_COUNTRIES in make_workflow_tables.pl.\n";
    exit 1;
} else {
    print "Which is correct\n";
}
	
print "Outputs:\n";
print "$MCC_COUNTRY_OUT_FILE\n";
print "$MAP_OUT_FILE\n";
print "$ORACLE_SQL_OUT_FILE\n";
